# Copyright (c) 2022, hzmes and contributors
# For license information, please see license.txt

import frappe
from frappe import _


def execute(filters=None):
	message = []
	# message.append()
	# message.append(f"入库: {c}")
	return get_columns(filters), get_data(filters), message


def get_columns(filters):
	columns = [
		{
			"label": _("BillType"),
			"fieldtype": "Data",
			"fieldname": "billtype",
			"width": 60,
		},
		{
			"label": _("SRM Material"),
			"fieldtype": "Data",
			"fieldname": "material",
			"width": 300,
		},{
			"label": _("BillNo"),
			"fieldtype": "Data",
			"fieldname": "billno",
			"width": 200,
		},{
			"label": _("PO"),
			"fieldtype": "Data",
			"fieldname": "po_order",
			"width": 200,
		},
	]

	return columns


def get_data(filters):
	# 获取filters
	# dt = []
	org, date_from, date_to = filters.get('org'), filters.get('creation_from'), filters.get('creation_to')
	# 先配置默认conditions再通过if判断是否增加条件项
	# billno = "".join(billno)
	# dt_instock = frappe.db.get_list('SRM Instock', filters =[["receive_org", "=", org], [ "creation", "between", [creation_from, creation_to] ]], fields = ["name", "currency", "`tabSRM Instock Sub`.material_number"])
	dt_instock = frappe.db.sql(
		f"""select '入库' as billtype, iq1.receive_org, iq1.name as billno, iq1.erp_billno, iq1.currency, iq2.name as row_name, CONCAT_WS(' : ', iq2.material_number, iq2.material_name) as material, iq2.receive_qty,
				iq2.material_model, iq2.po, iq2.erp_po_entryid, CONCAT_WS(' : ', po.erp_billno, po.fpoorderentry_fentryid) as poorder
				from `tabSRM Instock` iq1 
				join `tabSRM Instock Sub` iq2 on iq1.name = iq2.parent
				join (select erp_billno, fpoorderentry_fentryid, fpoorder_fid from `tabSRM Purchase Order` p1 join `tabSRM Purchase Order Sub` p2 on p1.name = p2.parent) po 
						on iq2.erp_po_entryid = po.FPOOrderEntry_FEntryID
			WHERE iq1.receive_org = '{org}' AND iq1.recieve_date >= '{date_from}' AND iq1.recieve_date <= '{date_to}';
		""", as_dict=True
	)
	dt_instock.append({'name': '小计'})

	# dt_instock_return = frappe.db.get_list('SRM Instock Return', filters =[["receive_org", "=", org], [ "creation", "between", [creation_from, creation_to] ]], fields = ["name", "currency", "`tabSRM Instock Return Sub`.material_number"])
	# dt_instock_return.append({'name': '小计'})

	return dt_instock
